package de.fmaul.dicheck;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

public class CreateDemoDatabase {

    private static Logger log = Logger.getLogger(CreateDemoDatabase.class);

    final static String driver = "org.hsqldb.jdbcDriver";
    final static String protocol = "jdbc:hsqldb:file:";
    final static String pathname = "demosuite/demodb/demodb";

    public static void main(String[] args) {
        Connection conn = connect();
        createDatabase(conn);
        close(conn);
    }

    private static void close(Connection conn) {

        try {
            if (conn != null) {
                conn.close();
            }
        } catch (final SQLException e) {
            log.error(e);
        }
    }

    private static Connection connect() {
        Connection conn = null;
        try {
            Class.forName(driver).newInstance();
            log.info("Loaded the jdbc driver " + driver);

            final String user = "sa";
            final String password = "";

            final Properties props = new Properties();
            props.put("user", user);
            props.put("password", password);
            props.put("shutdown", "true");
            conn = DriverManager.getConnection(protocol + pathname, props);
            log.info("Connected to server " + protocol + pathname);
        } catch (InstantiationException e) {
            log.error(e);
        } catch (IllegalAccessException e) {
            log.error(e);
        } catch (ClassNotFoundException e) {
            log.error(e);
        } catch (SQLException e) {
            log.error(e);
        }

        return conn;
    }

    public static void createDatabase(Connection conn) {

        try {
            final Statement s = conn.createStatement();

            s.execute("DROP TABLE Author IF EXISTS");
            s
                    .execute("CREATE TABLE Author(id INT, givenname varchar(50), surname varchar(50))");
            log.info("Created table author");
            s.execute("INSERT INTO Author VALUES(1, 'Joshua', 'Bloch')");
            s.execute("INSERT INTO Author VALUES(2, 'Martin', 'Fowler')");
            s.execute("INSERT INTO Author VALUES(3, 'Christian', 'Bauer')");
            s.execute("INSERT INTO Author VALUES(4, 'Gavin', 'King')");

            s.execute("DROP TABLE Book IF EXISTS");
            s.execute("CREATE TABLE Book(Id varchar(10), Title varchar(50)"
                    + ", isbn varchar(20))");
            log.info("Created table book");

            s.execute("INSERT INTO Book VALUES('343523', 'Java Puzzlers: Traps, "
                            + "Pitfalls, and Corner Cases', '978-0321336781')");
            s.execute("INSERT INTO Book VALUES('855d634', 'Effective Java: "
                    + "Programming Language Guide', '978-0201310054')");
            s.execute("INSERT INTO Book VALUES('655453', 'Hibernate in Action"+
                    " (In Action series)', '978-1932394153');");

            s.execute("DROP TABLE BookAuthors IF EXISTS");
            s.execute("CREATE TABLE BookAuthors(BookId varchar(10), AuthorId INT)");
            log.info("Created table BookAuthors");
            
            s.execute("INSERT INTO BookAuthors VALUES('343523', 1);");
            s.execute("INSERT INTO BookAuthors VALUES('655453', 3);");
            s.execute("INSERT INTO BookAuthors VALUES('655453', 4);");
            
            conn.commit();
        } catch (SQLException e) {
            log.error(e);
        }

    }
}